package cn.edu.hbwe.book.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.edu.hbwe.book.entity.Book;
import cn.edu.hbwe.book.entity.Notice;
import cn.edu.hbwe.book.utils.DBCPUtil;
import cn.edu.hbwe.book.utils.DuridUtil;

public class NoticeDao {
	//后台系统，查询所有的公告
	public List<Notice> getAllNotices() throws SQLException {
		List<Notice> notes= new ArrayList<Notice>();
		ResultSetHandler<List<Notice>> h=new BeanListHandler<Notice>(Notice.class);
		QueryRunner qr=new QueryRunner();
		Connection conn=DuridUtil.getConn();
		String sql = "select * from notice order by n_time desc limit 0,10";
		notes=qr.query(conn, sql, h);
		return notes;
	}
	public static List<Notice> findAll() {
		List<Notice> notes= new ArrayList<Notice>();
		ResultSetHandler<List<Notice>> h=new BeanListHandler<Notice>(Notice.class);
		QueryRunner qr=new QueryRunner();
		Connection conn=DuridUtil.getConn();
		String sql = "select * from Notice";
		try {
			notes=qr.query(conn, sql, h);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return notes;
		
	}
	//后台系统，添加公告
	public static void addNotice(Notice n) throws SQLException {	
		
		QueryRunner qr=new QueryRunner();
		try {
			String sql =  "insert into notice(title,details,n_time) values(?,?,?)";
			Connection conn=DuridUtil.getConn();
			qr.update(conn, sql, n.getTitle(),n.getDetails(),n.getN_time());//数据自己补
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	//后台系统，根据id查找公告
	public static Notice findNotice(String id) throws SQLException {
		
		
		Notice notice=null;
		ResultSetHandler<Notice> h=new BeanHandler<Notice>(Notice.class);
		QueryRunner qr=new QueryRunner();
			try {
				String sql = "select * from notice where n_id = ?";
				Connection conn=DuridUtil.getConn();
				notice=qr.query(conn,sql, h,id);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		
		
		return notice;
		
	}

	//后台系统，根据id修改单个公告
	public static void updateNotice(Notice n) throws SQLException {
	
		QueryRunner qr=new QueryRunner();
		try {
			String sql = "update notice set title=?,details=?,n_time=? where n_id=?";
			Connection conn=DuridUtil.getConn();
			qr.update(conn, sql, n.getTitle(),n.getDetails(),n.getN_time(),n.getN_id());//数据自己补
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	//后台系统，根据id删除公告
	public static void deleteNotice(String n_id) throws SQLException {
	
		QueryRunner qr=new QueryRunner();
		try {			
			Connection conn=DuridUtil.getConn();
			String sql =  "delete from notice where n_id = ?";
			int i=qr.update(conn, sql, n_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//前台系统，查询最新添加或修改的一条公告
	public Notice getRecentNotice() throws SQLException {
		
		Notice notice=null;
		ResultSetHandler<Notice> h=new BeanHandler<Notice>(Notice.class);
		QueryRunner qr=new QueryRunner();
			try {
				String sql = "select * from notice order by n_time desc limit 0,1";
				Connection conn=DuridUtil.getConn();
				notice=qr.query(conn,sql, h);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		
		
		return notice;
	}
}
